﻿using BingSpatialDataServiceExcelAddin.BSDSTools.FileTools;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;

namespace BingSpatialDataServiceExcelAddin.BSDSTools.Models
{
    public static class DataSourceTools
    {
        public static void ValidateDataSource(Excel.Worksheet sheet, out bool isValid, out bool includesSchemaInfo, out string notes, out ColumnHeader header)
        {
            StringBuilder validationNotes = new StringBuilder();

            //Lose focus of the active cell in case user is editting formula.
            if (Globals.ThisAddIn.Application.ActiveCell != null)
            {
                Globals.ThisAddIn.Application.ActiveCell.Activate();
                Globals.ThisAddIn.Application.ActiveCell.FindNext(Type.Missing);
            }

            includesSchemaInfo = false;
            isValid = true;

            //Check if the Schema Version Info is included in the document
            Excel.Range range = sheet.get_Range("A1", "C1") as Excel.Range;
            object[,] values = range.Value2 as object[,];

            string cell1 = (string)values[1, 1];

            if (cell1 == null)
            {
                isValid = false;
                header = null;
                notes = "Error: First cell of Data Source is empty.";
                return;
            }

            if (cell1.StartsWith("Bing Spatial Data Services", StringComparison.OrdinalIgnoreCase))
            {
                includesSchemaInfo = true;

                //Extract Entity Type Name
                string[] schemaInfo = cell1.Split(new char[] { ',' });
                string entityTypeName = string.Empty;

                if (schemaInfo.Length >= 3)
                {
                    //Entity Type Name should be the third value
                    entityTypeName = schemaInfo[2].Trim();
                }

                //If Entity Type Name is not found try checking the third cell
                if (string.IsNullOrWhiteSpace(entityTypeName))
                {
                    string cell3 = (string)values[1, 3];
                    if (!string.IsNullOrWhiteSpace(cell3))
                    {
                        entityTypeName = cell3.Trim();
                    }
                }

                //Update Settings with Entity Type Name
                if (!string.IsNullOrWhiteSpace(entityTypeName))
                {
                    Settings bsdsSettings = new Settings(Globals.ThisAddIn.Application.ActiveWorkbook);

                    //Check to see if Entity Type Name is different
                    if(string.IsNullOrWhiteSpace(bsdsSettings.EntityTypeName)){
                        bsdsSettings.EntityTypeName = entityTypeName;
                        bsdsSettings.Save(Globals.ThisAddIn.Application.ActiveWorkbook);

                        validationNotes.AppendLine("Warning: Updated Settings with Entity Type Name.");
                    }
                    else if (string.Compare(entityTypeName, bsdsSettings.EntityTypeName, true) != 0)
                    {
                        validationNotes.AppendLine("Warning: Entity Type Name in file does not match settings.");
                    }
                }
            }

            //Get the data source data
            object[,] cellValues = GetDataSource(sheet, includesSchemaInfo ? "A2" : "A1");

            int numCols = cellValues.GetLength(1);
            int numRows = cellValues.GetLength(0);

            header = ParseColumnHeader(cellValues, numCols);

            //Check to see if a Latitude Column Exists
            if (!header.ContainsName("Latitude"))
            {
                isValid = false;
                validationNotes.AppendLine("Error: Missing required column - Latitude");
            }

            //Check to see if a Longitude Column Exists
            if (!header.ContainsName("Longitude"))
            {
                isValid = false;
                validationNotes.AppendLine("Error: Missing required column - Longitude");
            }

            bool hasPrimaryKey = header.HasPrimaryKey(); 

            //If check to see if a primary key is defined or if an appropriate column exists as the primary key
            if (!hasPrimaryKey && !header.ContainsName("EntityID"))
            {
                //If EntityId exists use it as the primary key
                if(header.ContainsName("EntityID")){
                    hasPrimaryKey = true;
                    header[header.GetIndex("EntityID")].IsPrimaryKey = true;
                }else{ 
                    isValid = false;
                    validationNotes.AppendLine("Error: No primary key found.");
                }
            }

            if (!header.ContainsName("AddressLine") && !header.ContainsName("Locality") 
                && !header.ContainsName("PostalCode") && !header.ContainsName("AdminDistrict")
                && !header.ContainsName("CountryRegion"))
            {
                validationNotes.AppendLine("Warning: Unable to find any valid address columns:\r\nAddressLine, Locality, PostalCode, AdminDistrict, CountryRegion");
            }

            //Validate Header and format headers with correct data types.
            Excel.Range firstCell = sheet.get_Range(includesSchemaInfo ? "A2" : "A1", Type.Missing);
            Excel.Range lastCell = (Excel.Range)sheet.Cells[firstCell.Row,
                        sheet.Cells.Find("*",
                        (Excel.Range)sheet.Cells[1, 1],
                        Excel.XlFindLookIn.xlValues,
                        Excel.XlLookAt.xlWhole,
                        Excel.XlSearchOrder.xlByColumns,
                        Excel.XlSearchDirection.xlPrevious,
                        false, false, Type.Missing).Column];

            Excel.Range worksheetCells = sheet.get_Range(firstCell, lastCell);
            object[,] headerCells = worksheetCells.Value2 as object[,];

            Regex nameRx = new Regex(Constants.AlphaNumericUndscorePattern);
            Regex startsWithRx = new Regex("^[a-zA-Z_]");

            Dictionary<string, int> uniqueColumnName = new Dictionary<string, int>();
            string columnName, odataName;
            ColumnDefinition columnDef;

            for(int c = 1; c <= numCols; c++){
                columnDef = header[c - 1];

                if (columnDef.Name.Length > 50)
                {
                    isValid = false;
                    validationNotes.AppendFormat("Error: Column name \"{0}\" exceeds 50 character limit.\r\n", columnDef.Name);
                }

                if (columnDef.Name.StartsWith("__"))
                {
                    isValid = false;
                    validationNotes.AppendFormat("Error: Column name \"{0}\" cannot start with \"__\".\r\n", columnDef.Name);
                }

                if (!startsWithRx.IsMatch(columnDef.Name))
                {
                    isValid = false;
                    validationNotes.AppendFormat("Error: Column name \"{0}\" must start with letter or underscore\r\n", columnDef.Name);
                }

                if (!nameRx.IsMatch(columnDef.Name))
                {
                    isValid = false;
                    validationNotes.AppendFormat("Error: Column name \"{0}\" contains non-alphanumeric or underscore characters.\r\n", columnDef.Name);
                }

                if (string.Compare(columnDef.Name, "Entity", true) == 0)
                {
                    isValid = false;
                    validationNotes.Append("Error: Column name \"Entity\" is a reserved name.\r\n");
                }

                if (uniqueColumnName.ContainsKey(columnDef.Name))
                {
                    isValid = false;
                    validationNotes.AppendFormat("Error: Duplicate column name \"{0}\" found.\r\n", columnDef.Name);
                }
                else
                {
                    uniqueColumnName.Add(columnDef.Name, 0);
                }

                //Update Column Header Name
                if(columnDef.IsPrimaryKey){
                    odataName = string.Format("{0}({1},primaryKey)", columnDef.Name, columnDef.TypeName);
                }else{
                    if (string.Compare(columnDef.Name, "Latitude", true) == 0 ||
                        string.Compare(columnDef.Name, "Longitude", true) == 0)
                    {
                        odataName = string.Format("{0}({1})", columnDef.Name, DataTypes.Double);
                    }
                    else
                    {
                        odataName = string.Format("{0}({1})", columnDef.Name, columnDef.TypeName);
                    }
                }

                columnName = headerCells[1, c] as string;
                if (string.Compare(columnName, odataName) != 0)
                {
                    worksheetCells.Cells.set_Item(1, c, odataName);
                }
            }

            //Verify data type counts are within limits (350 properties + latitude and longtitude)
            if (numCols > 352)
            {
                validationNotes.Append("Error: Data source exceeds maximium number of properties of 350 not including Latitude and Longitude).");
            }

            //Check for duplicate primary key values. Primary keys must be unique.
            if(hasPrimaryKey){
                object key;

                Dictionary<object, int> uniqueKeyStore = new Dictionary<object, int>();
                List<string> duplicateKeys = new List<string>();
                int primaryKeyIdx = header.GetPrimaryKeyIndex() + 1;

                for (int r = 2; r <= numRows; r++)
                {
                    key = cellValues[r, primaryKeyIdx];
                    if (key == null)
                    {
                        isValid = false;
                        validationNotes.AppendFormat("Error: Null Primary Key in Row {0}\r\n", r + ((includesSchemaInfo) ? 2: 1));
                    }
                    else if(uniqueKeyStore.ContainsKey(key)){
                        duplicateKeys.Add(key as string);
                    }else{
                        uniqueKeyStore.Add(key, 0);
                    }
                }

                if (duplicateKeys.Count > 0)
                {
                    isValid = false;
                    validationNotes.AppendFormat("Error: Duplicate Primary Keys Found\r\n{0}", string.Join(", ", duplicateKeys.ToArray()));
                }
            }

            notes = validationNotes.ToString();
        }

        public static object[,] GetDataSource(Excel.Worksheet sheet, string startingCell)
        {
            Excel.Range worksheetCells = GetDataRange(sheet, startingCell);
            return worksheetCells.Value2 as object[,];
        }

        public static Excel.Range GetDataRange(Excel.Worksheet sheet, string startingCell)
        {
            //Lose focus of the active cell in case user is editting formula.
            if (Globals.ThisAddIn.Application.ActiveCell != null)
            {
                Globals.ThisAddIn.Application.ActiveCell.Activate();
                Globals.ThisAddIn.Application.ActiveCell.FindNext(Type.Missing);
            }

            Excel.Range firstCell = sheet.get_Range(startingCell, Type.Missing);
            Excel.Range lastCell = (Excel.Range)sheet.Cells[sheet.Cells.Find("*",
                       (Excel.Range)sheet.Cells[1, 1],
                       Excel.XlFindLookIn.xlValues,
                       Excel.XlLookAt.xlWhole,
                       Excel.XlSearchOrder.xlByRows,
                       Excel.XlSearchDirection.xlPrevious,
                       false, false, Type.Missing).Row,
                   sheet.Cells.Find("*",
                       (Excel.Range)sheet.Cells[1, 1],
                       Excel.XlFindLookIn.xlValues,
                       Excel.XlLookAt.xlWhole,
                       Excel.XlSearchOrder.xlByColumns,
                       Excel.XlSearchDirection.xlPrevious,
                       false, false, Type.Missing).Column];

            return sheet.get_Range(firstCell, lastCell);
        }

        public static ColumnHeader ParseColumnHeader(object[,] cellValues, int numCols)
        {
            ColumnHeader ch = new ColumnHeader();

            Regex ColumnHeaderRegex = new Regex(Constants.ColumnHeaderPattern);

            string name = string.Empty, typeName = string.Empty;
            bool isPrimaryKey = false;

            for (int i = 1; i <= numCols; i++)
            {
                isPrimaryKey = false;
                name = cellValues[1,i] as string;
                typeName = DataTypes.String;

                if (ColumnHeaderRegex.IsMatch(name))
                {
                    Match m = ColumnHeaderRegex.Match(name);

                    //Expect column header to look like:
                    //OData: EntityID(Edm.String,primarykey) or Longitude(Edm.Double)
                    //MapPoint: EntityID(long) or AddressLine(String)
                    //Multimap: client_id()
                    if (m.Success && m.Groups.Count >= 3)
                    {
                        name = m.Groups[1].Value;
                        typeName = m.Groups[2].Value;

                        if (typeName.IndexOf(",") > -1)
                        {
                            typeName = typeName.Substring(0, typeName.IndexOf(","));
                        }

                        if (m.Groups[2].Value.IndexOf("primaryKey", StringComparison.OrdinalIgnoreCase) > -1)
                        {
                            isPrimaryKey = true;
                        }
                    }
                }

                //Validate Data Type
                switch (typeName.ToLowerInvariant())
                {
                    case "edm.int64":
                    case "int":
                    case "long":
                        typeName = DataTypes.Long;
                        break;
                    case "edm.double":
                    case "float":
                    case "double":
                        typeName = DataTypes.Double;
                        break;
                    case "edm.boolean":
                    case "bool":
                    case "boolean":
                        typeName = DataTypes.Boolean;
                        break;
                    case "edm.datetime":
                    case "date":
                    case "datetime":
                        typeName = DataTypes.DateTime;
                        break;
                    case "edm.string":
                    case "varchar":     //Multimap
                    case "text":        //Multimap
                    default:
                        typeName = DataTypes.String;
                        break;
                }

                ch.Add(new ColumnDefinition()
                {
                    Name = name,
                    TypeName = typeName,
                    IsPrimaryKey = isPrimaryKey
                });
            }

            return ch;
        }
 
        public static string SafeStringValueCast(object[,] data, int row, int col){
            if(data != null && row != 0 && col != 0)
            {
                try
                {
                    object o = data[row, col];

                    if (data != null)
                    {
                        return (string)o;
                    }
                }
                catch { }
            }

            return null;
        }

        public static string ExportDelimitedDataSource(Excel.Worksheet sheet, Stream outputStream, char delimiter, Settings bsdsSettings)
        {
            StringBuilder logger = new StringBuilder();

            bool isValid, includesSchemaInfo;
            string notes = "";
            BSDSTools.FileTools.ColumnHeader header;

            DataSourceTools.ValidateDataSource(sheet, out isValid, out includesSchemaInfo, out notes, out header);

            if (isValid)
            {
                if(string.IsNullOrWhiteSpace(bsdsSettings.EntityTypeName)){
                    logger.Append("Entity Type Name not specified. Please add to settings.");
                    return logger.ToString();
                }

                using (DelimitedWriter writer = new DelimitedWriter(outputStream, header.Count, delimiter))
                {
                    //Add Bing Schema Information
                    writer.WriteLine(string.Format(Constants.BingDataSchemaType, bsdsSettings.EntityTypeName));

                    //Add Header Information
                    IList<string> cols = header.GetColumnNames(true);

                    //Write Header to new data source
                    writer.WriteRow(cols, false);

                    //Get the data source data. Skip header info.
                    object[,] cellValues = GetDataSource(sheet, includesSchemaInfo ? "A3" : "A2");

                    int numCols = cellValues.GetLength(1);
                    int numRows = cellValues.GetLength(0);
                    List<object> row;

                    string[] columnTypes = header.GetTypeNames();

                    int idIdx = header.GetPrimaryKeyIndex() + 1;
                    int latIdx = header.GetIndex("Latitude") + 1;
                    int lonIdx = header.GetIndex("Longitude") + 1;

                    List<string> skippedEntities = new List<string>();

                    for (int r = 1; r <= numRows; r++)
                    {
                        //Skip ungeocoded Entries
                        if (numCols >= latIdx && numCols >= lonIdx && !string.IsNullOrWhiteSpace(cellValues[r, latIdx].ToString()) && !string.IsNullOrWhiteSpace(cellValues[r, lonIdx].ToString()))
                        {
                            row = new List<object>();

                            for (int c = 1; c <= numCols; c++)
                            {
                                //Cast object to proper type.
                                row.Add(DataTypes.CastToSafeType(cellValues[r, c], columnTypes[c - 1]));
                            }

                            //Write updated line to data source
                            writer.WriteRow(row, true);
                        }
                        else
                        {
                            skippedEntities.Add(cellValues[r, idIdx].ToString());                            
                        }
                    }

                    if(skippedEntities.Count > 0){
                        logger.AppendFormat("Skipped {0} ungeocoded entities\r\n", skippedEntities.Count);
                        logger.Append(string.Join(", ",skippedEntities.ToArray()));
                    }
                }
            }
            else
            {
                logger.Append("Error: Data Source Invalid.\r\n");
                logger.Append(notes);
            }

            //return log
            return logger.ToString();
        }

        public static void GenerateDataSet(Excel.Worksheet sheet, out DataSet dataSet, out string log)
        {            
            StringBuilder logger = new StringBuilder();

            bool isValid, includesSchemaInfo;
            string notes = "";
            BSDSTools.FileTools.ColumnHeader header;

            DataSourceTools.ValidateDataSource(sheet, out isValid, out includesSchemaInfo, out notes, out header);
            Settings bsdsSettings = new Settings(Globals.ThisAddIn.Application.ActiveWorkbook);

            if (isValid)
            {
                if(string.IsNullOrWhiteSpace(bsdsSettings.EntityTypeName)){
                    logger.Append("Entity Type Name not specified. Please add to settings.");
                    log = logger.ToString();
                    dataSet = null;
                    return;
                }

                DataTable table = new DataTable(bsdsSettings.EntityTypeName);
                
                DataColumn column, primaryKey = null;

                //Define the header
                foreach (ColumnDefinition def in header)
                {
                    column = new DataColumn(def.Name, DataTypes.GetType(def.TypeName));
                    table.Columns.Add(column);

                    if (def.IsPrimaryKey)
                    {
                        primaryKey = column;
                    }
                }

                table.PrimaryKey = new DataColumn[] { primaryKey };

                //Get the data source data. Skip header info.
                object[,] cellValues = GetDataSource(sheet, includesSchemaInfo ? "A3" : "A2");

                int numCols = cellValues.GetLength(1);
                int numRows = cellValues.GetLength(0);
                List<object> row;

                string[] columnTypes = header.GetTypeNames();

                int idIdx = header.GetPrimaryKeyIndex() + 1;
                int latIdx = header.GetIndex("Latitude") + 1;
                int lonIdx = header.GetIndex("Longitude") + 1;

                List<string> skippedEntities = new List<string>();

                for (int r = 1; r <= numRows; r++)
                {
                    //Skip ungeocoded Entries
                    if (numCols >= latIdx && numCols >= lonIdx && !string.IsNullOrWhiteSpace(cellValues[r, latIdx].ToString()) && !string.IsNullOrWhiteSpace(cellValues[r, lonIdx].ToString()))
                    {
                        row = new List<object>();

                        for (int c = 1; c <= numCols; c++)
                        {
                            //Cast object to proper type.
                            row.Add(DataTypes.CastToSafeType(cellValues[r, c], columnTypes[c - 1]));
                        }

                        //Add data to table
                        table.Rows.Add(row.ToArray());
                    }
                    else
                    {
                        skippedEntities.Add(cellValues[r, idIdx].ToString());
                    }
                }

                dataSet = new System.Data.DataSet(bsdsSettings.DataSourceName);
                dataSet.Tables.Add(table);

                if (skippedEntities.Count > 0)
                {
                    logger.AppendFormat("Skipped {0} ungeocoded entities\r\n", skippedEntities.Count);
                    logger.Append(string.Join(", ", skippedEntities.ToArray()));
                }
            }
            else
            {
                logger.Append("Error: Data Source Invalid.\r\n");
                logger.Append(notes);

                dataSet = null;
            }

            log = logger.ToString();
        }
    }
}
